package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.TbCloseAccounts;
import com.pignest.hotel.dao.ReportDao;

@SuppressWarnings("unchecked")
public class ReportHibernateDao extends BaseHibernateDao implements ReportDao {

	@Override
	public List<Object> checkoutSummaryByConsume(final Date startTime, final Date endTime) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				String hql = "select a.type, sum(a.price) from TbAccounts a where a.endstate=:endState and a.endtime>=:startTime and a.endtime<:endTime";
				hql=hql+" and a.price<0";
				Query q = session.createQuery(hql);
				q.setString("endState", "是");
				q.setTimestamp("startTime", startTime);
				q.setTimestamp("endTime", endTime);
				return q.list();
			}
		});
	}
	
	public List<Object> checkoutSummaryByPayment(final Date startTime, final Date endTime) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				String hql = "select a.type, sum(a.accounts) from TbCloseAccounts a where a.time>=:startTime and a.time<:endTime group by a.type";
				Query q = session.createQuery(hql);
				q.setTimestamp("startTime", startTime);
				q.setTimestamp("endTime", endTime);
				return q.list();
			}
		});
	}

	@Override
	public List<TbCloseAccounts> checkoutDetail(final Date startTime, final Date endTime) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				String hql = "from TbCloseAccounts a where a.time>=:startTime and a.time<:endTime";
				Query q = session.createQuery(hql);
				q.setTimestamp("startTime", startTime);
				q.setTimestamp("endTime", endTime);
				q.setFirstResult(0);
				q.setMaxResults(1000);
				return q.list();
			}
		});
	}
	
	public long memberCharge(final Date startTime, final Date endTime) {
		Long v = (Long)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				String hql = "select sum(a.price) from Memberchargehistrory a where a.chargeTime>=:startTime and a.chargeTime<:endTime and a.price<0";
				Query q = session.createQuery(hql);
				q.setTimestamp("startTime", startTime);
				q.setTimestamp("endTime", endTime);
				return q.uniqueResult();
			}
		});
		if (v == null)
			return 0;
		return v.longValue();
	}

}
